---
title: Filter
icon: Funnel
---

<MetaData
  lang="en-US"
  meta={{
    preset: [{
      client: '@univerjs/preset-sheets-filter',
      locale: '@univerjs/preset-sheets-filter/locales/en-US',
      style: '@univerjs/preset-sheets-filter/lib/index.css',
    }],
    plugins: [{
      client: '@univerjs/sheets-filter',
      facade: '@univerjs/sheets-filter/facade',
    }, {
      client: '@univerjs/sheets-filter-ui',
      locale: '@univerjs/sheets-filter-ui/locale/en-US',
      style: '@univerjs/sheets-filter-ui/lib/index.css',
    }],
    server: 'No',
  }}
/>

The Filter feature allows users to filter data in spreadsheets to quickly find and analyze specific information. It supports various filtering conditions and operations, helping users process data more efficiently.

## Preset Mode

### Installation

```package-install
npm install @univerjs/preset-sheets-filter
```

### Usage

```typescript
import { UniverSheetsCorePreset } from '@univerjs/preset-sheets-core'
import UniverPresetSheetsCoreEnUS from '@univerjs/preset-sheets-core/locales/en-US'
import { UniverSheetsFilterPreset } from '@univerjs/preset-sheets-filter' // [!code ++]
import UniverPresetSheetsFilterEnUS from '@univerjs/preset-sheets-filter/locales/en-US' // [!code ++]
import { createUniver, LocaleType, mergeLocales } from '@univerjs/presets'

import '@univerjs/preset-sheets-core/lib/index.css'
import '@univerjs/preset-sheets-filter/lib/index.css' // [!code ++]

const { univerAPI } = createUniver({
  locale: LocaleType.En_US,
  locales: {
    [LocaleType.En_US]: mergeLocales(
      UniverPresetSheetsCoreEnUS,
      UniverPresetSheetsFilterEnUS, // [!code ++]
    ),
  },
  presets: [
    UniverSheetsCorePreset(),
    UniverSheetsFilterPreset(), // [!code ++]
  ],
})
```

### Presets and Configuration

```typescript
interface IUniverSheetsFilterPresetConfig {
  /**
   * Whether to display the filter sync switch in the filter panel.
   * This configuration can be used in a collaboration scenario to allow users to choose whether to enable filter synchronization.
   * @default false
   */
  enableSyncSwitch?: boolean
}
```

## Plugin Mode

### Installation

```package-install
npm install @univerjs/sheets-filter @univerjs/sheets-filter-ui
```

### Usage

```typescript
import { LocaleType, mergeLocales, Univer } from '@univerjs/core'
import { UniverSheetsFilterPlugin } from '@univerjs/sheets-filter' // [!code ++]
import { UniverSheetsFilterUIPlugin } from '@univerjs/sheets-filter-ui' // [!code ++]
import SheetsFilterUIEnUS from '@univerjs/sheets-filter-ui/locale/en-US' // [!code ++]

import '@univerjs/sheets-filter-ui/lib/index.css' // [!code ++]

import '@univerjs/sheets-filter/facade' // [!code ++]

const univer = new Univer({
  locale: LocaleType.En_US,
  locales: {
    [LocaleType.En_US]: mergeLocales(
      SheetsFilterUIEnUS, // [!code ++]
    ),
  },
})

univer.registerPlugin(UniverSheetsFilterPlugin) // [!code ++]
univer.registerPlugin(UniverSheetsFilterUIPlugin) // [!code ++]
```

### Plugins and Configuration

```typescript
interface IUniverSheetsFilterConfig {
  /**
   * Whether to display the filter sync switch in the filter panel.
   * This configuration can be used in a collaboration scenario to allow users to choose whether to enable filter synchronization.
   * @default false
   */
  enableSyncSwitch?: boolean
}
```

## Facade API

Complete Facade API type definitions can be found in the [FacadeAPI](https://reference.univer.ai/en-US).

### Get Filter

Returns an [`FFilter`](https://reference.univer.ai/en-US/classes/FFilter) object. If the sheet does not have a filter, it returns `null`.

- On the `FWorksheet` object, use [`getFilter()`](https://reference.univer.ai/en-US/classes/FWorksheet#getfilter)
- On the `FRange` object, use [`getFilter()`](https://reference.univer.ai/en-US/classes/FRange#getfilter)

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()

// Get filter from FWorksheet
const fWorksheet = fWorkbook.getActiveSheet()
const fFilter = fWorksheet.getFilter()
fFilter?.getRange().getA1Notation()

// Get filter from FRange
const fRange = fWorksheet.getRange('A1:D14')
const fFilter2 = fRange.getFilter()
fFilter2?.getRange().getA1Notation()
```

### Create Filter

Creates an [`FFilter`](https://reference.univer.ai/en-US/classes/FFilter) object. If the sheet already has a filter, it returns `null`.

- On the `FRange` object, use [`createFilter()`](https://reference.univer.ai/en-US/classes/FRange#createfilter)

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
const fRange = fWorksheet.getRange('A1:D14')
let fFilter = fRange.createFilter()

// If the worksheet already has a filter, remove it and create a new filter.
if (!fFilter) {
  fWorksheet.getFilter().remove()
  fFilter = fRange.createFilter()
}
fFilter.getRange().getA1Notation()
```

### Remove Filter

[`FFilter.remove()`](https://reference.univer.ai/en-US/classes/FFilter#remove) 方法移除筛选器。

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()
fWorksheet.getFilter()?.remove()
```

### Get Column Filter Criteria

[`FFilter.getColumnFilterCriteria(column)`](https://reference.univer.ai/en-US/classes/FFilter#getcolumnfiltercriteria) 方法返回指定列的筛选条件。

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Set some values of the range C1:F10
const fRange = fWorksheet.getRange('C1:F10')
fRange.setValues([
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
  [6, 7, 8, 9],
  [7, 8, 9, 10],
  [8, 9, 10, 11],
  [9, 10, 11, 12],
  [10, 11, 12, 13],
])

// Set some values of the range C1:F10
let fFilter = fRange.createFilter()

// If the filter already exists, remove it and create a new one
if (!fFilter) {
  fRange.getFilter().remove()
  fFilter = fRange.createFilter()
}

// Set the filter criteria of the column C, filter out the rows that are not 1, 5, 9
const column = fWorksheet.getRange('C:C').getColumn()
fFilter.setColumnFilterCriteria(column, {
  colId: 0,
  filters: {
    filters: ['1', '5', '9'],
  },
})

// Print the filter criteria of the column C and D
fFilter.getColumnFilterCriteria(column) // { colId: 0, filters: { filters: ['1', '5', '9'] } }
fFilter.getColumnFilterCriteria(column + 1) // undefined
```

### Set Column Filter Criteria

[`FFilter.setColumnFilterCriteria(column, criteria)`](https://reference.univer.ai/en-US/classes/FFilter#setcolumnfiltercriteria) method sets the filter criteria of the specified column.

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Set some values of the range C1:F10
const fRange = fWorksheet.getRange('C1:F10')
fRange.setValues([
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
  [6, 7, 8, 9],
  [7, 8, 9, 10],
  [8, 9, 10, 11],
  [9, 10, 11, 12],
  [10, 11, 12, 13],
])

// Create a filter on the range C1:F10
let fFilter = fRange.createFilter()

// If the filter already exists, remove it and create a new one
if (!fFilter) {
  fRange.getFilter().remove()
  fFilter = fRange.createFilter()
}

// Set the filter criteria of the column C, filter out the rows that are not 1, 5, 9
const column = fWorksheet.getRange('C:C').getColumn()
fFilter.setColumnFilterCriteria(column, {
  colId: 0,
  filters: {
    filters: ['1', '5', '9'],
  },
})
```

### Remove Column Filter Criteria

- [`FFilter.removeColumnFilterCriteria(column)`](https://reference.univer.ai/en-US/classes/FFilter#removecolumnfiltercriteria) method removes the filter criteria of the specified column.
- [`FFilter.removeFilterCriteria()`](https://reference.univer.ai/en-US/classes/FFilter#removefiltercriteria) method removes the filter criteria of all columns.

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Set some values of the range C1:F10
const fRange = fWorksheet.getRange('C1:F10')
fRange.setValues([
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
  [6, 7, 8, 9],
  [7, 8, 9, 10],
  [8, 9, 10, 11],
  [9, 10, 11, 12],
  [10, 11, 12, 13],
])

// Create a filter on the range C1:F10
let fFilter = fRange.createFilter()

// If the filter already exists, remove it and create a new one
if (!fFilter) {
  fRange.getFilter().remove()
  fFilter = fRange.createFilter()
}

// Set the filter criteria of the column C, filter out the rows that are not 1, 5, 9
const column = fWorksheet.getRange('C:C').getColumn()
fFilter.setColumnFilterCriteria(column, {
  colId: 0,
  filters: {
    filters: ['1', '5', '9'],
  },
})

// Clear the filter criteria of the column C after 3 seconds
setTimeout(() => {
  fFilter.removeColumnFilterCriteria(column)
  // Or use fFilter.removeFilterCriteria() to remove all column filter criteria
}, 3000)
```

### Get Filtered Out Rows

[`FFilter.getFilteredOutRows()`](https://reference.univer.ai/en-US/classes/FFilter#getfilteredoutrows) method returns an array containing the indexes of the rows that are filtered out.

```typescript
const fWorkbook = univerAPI.getActiveWorkbook()
const fWorksheet = fWorkbook.getActiveSheet()

// Set some values of the range C1:F10
const fRange = fWorksheet.getRange('C1:F10')
fRange.setValues([
  [1, 2, 3, 4],
  [2, 3, 4, 5],
  [3, 4, 5, 6],
  [4, 5, 6, 7],
  [5, 6, 7, 8],
  [6, 7, 8, 9],
  [7, 8, 9, 10],
  [8, 9, 10, 11],
  [9, 10, 11, 12],
  [10, 11, 12, 13],
])

// Create a filter on the range C1:F10
let fFilter = fRange.createFilter()

// If the filter already exists, remove it and create a new one
if (!fFilter) {
  fRange.getFilter().remove()
  fFilter = fRange.createFilter()
}

// Set the filter criteria of the column C, filter out the rows that are not 1, 5, 9
const column = fWorksheet.getRange('C:C').getColumn()
fFilter.setColumnFilterCriteria(column, {
  colId: 0,
  filters: {
    filters: ['1', '5', '9'],
  },
})

// Print the filtered out rows
fFilter.getFilteredOutRows() // [1, 2, 3, 5, 6, 7, 9]
```

### Event Listeners

Complete event type definitions can be found in the [Events](https://reference.univer.ai/en-US/classes/FEventName).

`SheetRangeFiltered` event is triggered when the column filter criteria changes.

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.SheetRangeFiltered, (params) => {
  const { workbook, worksheet, col, criteria } = params
})

// Remove the event listener, use `disposable.dispose()`
```

`SheetBeforeRangeFilter` event is triggered before the column filter criteria changes.

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.SheetBeforeRangeFilter, (params) => {
  const { workbook, worksheet, col, criteria } = params

  // Cancel the filter criteria change operation
  params.cancel = true
})

// Remove the event listener, use `disposable.dispose()`
```

`SheetRangeFilterCleared` event is triggered when the criteria is cleared.

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.SheetRangeFilterCleared, (params) => {
  const { workbook, worksheet } = params
})

// Remove the event listener, use `disposable.dispose()`
```

`SheetBeforeRangeFilterClear` event is triggered before the criteria is cleared.

```typescript
const disposable = univerAPI.addEvent(univerAPI.Event.SheetBeforeRangeFilterClear, (params) => {
  const { workbook, worksheet } = params

  // Cancel the filter clear operation
  params.cancel = true
})

// Remove the event listener, use `disposable.dispose()`
```
